﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Newtonsoft.Json;

    /// <summary>
    /// 数据库帮助类
    /// </summary>
    public class DBHelper
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        //private readonly static string _connectionString = "server=.;uid=sa;pwd=123456;database=HomeStaticsDB";       
        private readonly static string _connectionString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;

        /// <summary>
        /// 执行SQL（首行首列）
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>object</returns>
        public static object Scalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                return cmd.ExecuteScalar();
            }
        }

        /// <summary>
        /// 执行SQL（增、删、改）
        /// </summary>
        /// <param name="sql">>SQL语句</param>
        /// <returns>int</returns>
        public static int Excute(string sql)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                return cmd.ExecuteNonQuery();
            }           
        }

        /// <summary>
        /// 执行SQL（查询）
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>DataTable</returns>
        public static DataTable Query(string sql)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable ds = new DataTable();
                da.Fill(ds);
                return ds;
            }
        }

        /// <summary>
        /// 获得列表(查询)
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <returns>List<T></returns>
        public static List<T> GetList<T>(string sql)
        {
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable ds = new DataTable();
                da.Fill(ds);

                string json =  JsonConvert.SerializeObject(ds);
                List<T> list = JsonConvert.DeserializeObject<List<T>>(json);

                return list;
            }
        }
    }

